How to Concatenate Strings in Oracle
Oracle offers two ways to concatenate strings. The first uses the || operator:
select 'Join these ' || 'strings with a number ' || 23
from dual;
result |
|---|
Join these strings with a number 23 |
You can see from above that Oracle took care of transforming the number to a string to attach it to the rest. Note that you also need to consciously add spaces to make the string readable. For example, in the customer table to join the first and last names of the customers you have to add a single space in the concatenation:
select first_name||' '||last_name as customer_name from customer;
customer_name |
|---|
MARY SMITH |
PATRICIA JOHNSON |
LINDA WILLIAMS |
BARBARA JONES |
ELIZABETH BROWN |
When concatenating strings Oracle considers the NULL value as the empty string, then any concatenation expression will return a value even when one element in the expression is NULL.
select 'Null concatenated with ||'||' something will return ' || 'something' || null
from dual;
result |
|---|
| `Null concatenated with |
You can use concat instead of || operator in Oracle:
select concat('Concat() works', ' identical to || but only supports two strings')
from dual;
concat |
|---|
| `Concat() works identical to |
In all the previous examples we used a table called dual, this is because in Oracle the from clause is mandatory. The dual table is a pseudo-table you can use in any query when you want to calculate an expression not based on table columns.
